<?php session_start(); ?>
<!DOCTYPE HTML>
<html>
    
<head>
	<title>Stewie's Market</title>
	<link rel="stylesheet" href="cs2102.css">
</head>

<body>
<div class = "container">
	<!-- Site navigation menu -->
	<?php include 'nav.php'; 

	function init_db()
	{
        // Open database connection
		$dbHostPort="1521";
		$dbHost = "sid3.comp.nus.edu.sg";
		$dbServiceName = "sid3.comp.nus.edu.sg";
		$usr = "A0103863";
		$pswd = "cs2102std";
		$dbConnStr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=".$dbHost.")(PORT=".$dbHostPort."))(CONNECT_DATA=(SERVICE_NAME=".$dbServiceName.")))";
		$dbConn = oci_connect($usr,$pswd,$dbConnStr);

		if(!$dbConn)
		{
			$err = oci_error();
			print('Could not establish a database connection: ' . $err['message']);
		}
		return $dbConn;
	}


	function fetch_results_for_query($db, $query)
	{
		$stmt = oci_parse($db,$query);
		if (!oci_execute($stmt)) {
			$err = oci_error($stmt);
			print('Query failed: '.$err['message']);
		}
        // Create result array with one entry per row
		$res = array();
		while ($next = oci_fetch_row($stmt))
            array_push($res, $next); // append to res array
        
        return $res;
    }

    function print_results($res)
    {

    	if (count($res) > 0){
    		echo "<table width='100%'>";
    		echo "<col width='14%'>";
    		echo "<col width='50%'>";
    		echo "<col width='50%'>";

    		echo "<tr>";
    		echo "<td> Time: </td>";
    		echo "<td> Subject:</td>";
    		echo "<td> Sender: </td>";    

    		echo "</tr>";
            foreach ($res as $col) { // for every row
            	echo "<tr>";

            	echo "<td>".$col[3]."</td>";
            	echo "<td><a href='message_page.php?subject=".$col[0]."&body=".$col[1]."&sender=".$col[2]."&time=".$col[3]."&matric=".$col[4]."'>".$col[0]."</a></td>";
            	echo "<td>".$col[2]."</td>";
            	echo "</tr>";
            }
            echo "</table>";
        }else{
        	print "You have no messages!";
        }
    }
    ?>

    <br /><h1>Conversations</h1>
    <?php

    $db = init_db();
    $sql = "SELECT a.subject, a.body, s.name, to_char(a.message_time, 'DD MON HH24:MI'), s.matric FROM message a, student s WHERE s.matric = a.sender and a.receiver='".$_SESSION['user']."' ORDER BY a.message_time DESC";
               // echo $sql;
    $res = fetch_results_for_query($db, $sql);

    print_results($res);

    ?>
</div>

<!-- Sign and date the page, it's only polite! -->
<address>Made by Project group 31 for CS2102, NUS 2013.</address>
</div>
</div>
</body>
</html>